package org.kitten.core.util;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 * 读Excel的工具类.
 * 
 * @author sb.yin(uniquejava@gmail.com)
 * @version 1.0
 * @since 2011-4-8
 */
public class ExcelCommonReader {
    private static final Log logger = LogFactory.getLog(ExcelCommonReader.class);
	/**
	 * 工作簿
	 */
	private HSSFWorkbook book;

	private String[] sheetNames;

	private int sheetCount = 0;
	/**
	 * 表头的行数
	 */
	private int headerRowCount;
	/**
	 * 取哪些列的数据
	 */
	private int[] columnIndex;

	public ExcelCommonReader(String fileName, int headerRows,
			int... columnIndex) throws Exception {
		InputStream is = new FileInputStream(fileName);
		this.book = new HSSFWorkbook(is);
		this.sheetCount = book.getNumberOfSheets();
		this.sheetNames = new String[sheetCount];
		for (int i = 0; i < sheetNames.length; i++) {
			sheetNames[i] = book.getSheetName(i);
		}
		this.headerRowCount = headerRows;
		this.columnIndex = columnIndex;
	}

	public int getSheetCount() {
		return sheetCount;
	}

	public void setSheetCount(int sheetCount) {
		this.sheetCount = sheetCount;
	}

	/**
	 * 处理工作簿中的一张表单
	 * 
	 * @param sheetIndex
	 *            表单下标,从0开始
	 * @throws Exception
	 */
	public List<Map<String, String>> handleSheet(int sheetIndex)
			throws Exception {
		HSSFSheet sheet = book.getSheetAt(sheetIndex);
		int rowIndex = 0;
		List<Map<String, String>> result = new ArrayList<Map<String, String>>();
		for (Iterator<HSSFRow> it = sheet.rowIterator(); it.hasNext();) {
			HSSFRow row = it.next();
			rowIndex++;
			if (rowIndex <= headerRowCount) {
				continue;
			}
			Map<String, String> rowMap = new HashMap<String, String>();
			for (int i = 0; i < columnIndex.length; i++) {
				HSSFCell cell = row.getCell((short) columnIndex[i]);
				String value = "";
				try {
					int type = cell.getCellType();
					if (type == HSSFCell.CELL_TYPE_STRING) {
						value = cell.getStringCellValue();
					} else if (type == HSSFCell.CELL_TYPE_NUMERIC) {
						if (HSSFDateUtil.isCellDateFormatted(cell)) {
							value = String.valueOf(cell.getDateCellValue());
						} else {
							value = String.valueOf(cell.getNumericCellValue());
							if (value.matches("^[0-9]+(\\.0+)$")) {
								value = value.substring(0,value.lastIndexOf("."));
							}
						}
					} else if (type == HSSFCell.CELL_TYPE_BLANK) {
						value = "";
					} else if (type == HSSFCell.CELL_TYPE_BOOLEAN) {
						value = String.valueOf(cell.getBooleanCellValue());
					} else if (type == HSSFCell.CELL_TYPE_ERROR) {
						value = String.valueOf(cell.getErrorCellValue());
					} else if (type == HSSFCell.CELL_TYPE_FORMULA) {
						value = "";
					} else {
						value = "";
					}
				} catch (Exception e) {
					logger.error(ErrorUtil.getError(e));
				}
				rowMap.put(String.valueOf(i),value);
			}
			result.add(rowMap);
		}
		return result;
	}

	public static void main(String[] args) throws Exception {
		ExcelCommonReader ecr = new ExcelCommonReader(
				"c:/student.xls", 1, 0, 1,2,3,4,5);
		List<Map<String, String>> data = ecr.handleSheet(0);
		for (Iterator iterator = data.iterator(); iterator.hasNext();) {
			Map<String, String> map = (Map<String, String>) iterator.next();
			System.out.println(map.get("0") + "," + map.get("1")+ "," + map.get("2")+ "," + map.get("3")+ "," + map.get("4")+ "," + map.get("5"));
		}
	}
}
